CREATE PROCEDURE dbo.sp_GetPostList
AS
	SET NOCOUNT ON;
SELECT     P.CategoryID, P.PostID, P.Name, P.CreateDate, P.Subject, R.ReplyCounts, R.LastUpdate, P.Hits, P.ParentPostID
FROM         (SELECT     Users.NickName AS Name, F.CategoryID, F.NumberOfVisits AS Hits, F.PostID, F.ParentPostID, F.Subject, F.CreateDate
                       FROM          ForumPosts AS F INNER JOIN
                                              Users ON F.UserID = Users.UserID
                       WHERE      (F.ParentPostID IS NULL)) AS P LEFT OUTER JOIN
                          (SELECT     R1.PostID, COUNT(R1.PostID) AS ReplyCounts, MAX(R2.CreateDate) AS LastUpdate
                            FROM          ForumPosts AS R1 INNER JOIN
                                                   ForumPosts AS R2 ON R1.PostID = R2.ParentPostID
                            GROUP BY R1.PostID) AS R ON P.PostID = R.PostID
                            where P.CategoryID='33' order by R.LastUpdate desc
                            
GO
